package com.zhiyou.dao;

import com.zhiyou.model.Contract;
import com.zhiyou.model.Lessee;
import com.zhiyou.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class LesseeDao {
    public int getLesseeNum(HashMap<String,String> keywordMap) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        int total=0;
        String keyword = keywordMap.get("keyword");
        String sql="select count(lid) total from lessee where lname like concat(\"%\",?,\"%\")";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword==null?"":keyword);
            rs=ps.executeQuery();
            while (rs.next()){
                total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return total;
    }

    public List<Lessee> getLessee(int pageNoL, int pageSize,HashMap<String,String> keywordMap) {
        ArrayList<Lessee> lessees = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        String keyword = keywordMap.get("keyword");
        String sql="select * from lessee where lname like concat(\"%\",?,\"%\") limit ?,?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword==null?"":keyword);
            ps.setInt(2,(pageNoL-1)*pageSize);
            ps.setInt(3,pageSize);
            rs = ps.executeQuery();
            while(rs.next()){
                Lessee lessee=new Lessee();
                lessee.setLid(rs.getInt("lid"));
                lessee.setLname(rs.getString("lname"));
                lessee.setLtel(rs.getString("ltel"));
                lessee.setLsex(rs.getInt("lsex"));
                lessee.setLnp(rs.getString("lnp"));
                lessee.setLidCard(rs.getString("lidCard"));
                lessee.setLaddTime(rs.getDate("laddTime"));
                lessees.add(lessee);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return lessees;
    }

    public void deleteById(int lid) {
        Connection conn = DBUtil.getConn();
        String sql="delete from lessee where lid=?";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,lid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public void addNew(Lessee lessee) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="insert into lessee(lname,ltel,lsex,lnp,lidCard,laddTime) values(?,?,?,?,?,?) ";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,lessee.getLname());
            ps.setString(2,lessee.getLtel());
            ps.setInt(3,lessee.getLsex());
            ps.setString(4,lessee.getLnp());
            ps.setString(5,lessee.getLidCard());
            ps.setDate(6,new java.sql.Date(lessee.getLaddTime().getTime()));
            System.out.println("日志: sql"+ps.toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public Lessee selectById(int lid) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        Lessee lessee=null;
        String sql="select * from lessee where lid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,lid);
            rs=ps.executeQuery();
            while (rs.next()){
                lessee = new Lessee();
                lessee.setLid(rs.getInt("lid"));
                lessee.setLname(rs.getString("lname"));
                lessee.setLtel(rs.getString("ltel"));
                lessee.setLsex(rs.getInt("lsex"));
                lessee.setLnp(rs.getString("lnp"));
                lessee.setLidCard(rs.getString("lidCard"));
                lessee.setLaddTime(rs.getDate("laddTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return lessee;
    }

    public void updateNew(Lessee lessee) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="update lessee set lname=?,ltel=?,lnp=?,lidCard=?,laddTime=? where lid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,lessee.getLname());
            ps.setString(2,lessee.getLtel());
            ps.setString(3,lessee.getLnp());
            ps.setString(4,lessee.getLidCard());
            ps.setDate(5,new java.sql.Date(lessee.getLaddTime().getTime()));
            ps.setInt(6,lessee.getLid());
            System.out.println("日志 : sql="+ps.toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }
}
